#!/usr/bin/env python
# coding=utf-8

import MySQLdb
import time
import os
import json

def function_host_input():
    # 连接数据库
    conn = MySQLdb.connect(
                    host='localhost',
                    port=3306,
                    user='root',
                    passwd='cnv6201',
                    db='PRESENTATION',
                    charset='utf8',
                )
    cur = conn.cursor()

    # 清空数据库中表格内的信息
    cur.execute("delete from presentation_ssh_connect")
    cur.execute("delete from presentation_vm_connect")
    cur.execute("delete from presentation_filesystem")

    # 打开主机行为SSH和虚拟机的JSON文件读取数据
    f = open(os.getcwd() + '/JsonFileLog/report_log_host.json', "r", encoding='UTF-8')
    data = json.load(f)


    # 向数据库中传入SSH的相关信息
    log_set = set()
    for i in range(len(data['layer_info']['localhost'])):
        localhost = data['layer_info']['localhost'][i]['localHost']
        remotehost = data['layer_info']['localhost'][i]['foreignHost']
        username = data['layer_info']['localhost'][i]['userName']
        conntime = data['layer_info']['localhost'][i]['hostTime']
        log_set.add((localhost, remotehost, username, conntime))

    # sort by time
    log_list = list(log_set)
    log_list.sort(key=lambda x: x[3])

    # read log_list and write it into database
    for obj in log_list:
        if isinstance(obj, tuple):
            localhost = obj[0]
            remotehost = obj[1]
            username = obj[2]
            conntime = obj[3]
            cur.execute(
                "insert into presentation_ssh_connect(localhost,remotehost,username,conntime) values(%s,%s,%s,%s)",
                [localhost, remotehost, username, conntime])

    # 向数据库中传入虚拟机的相关信息
    log_set = set()
    for i in range(len(data['layer_info']['vm'])):
        vmname = data['layer_info']['vm'][i]['vmName']
        port = data['layer_info']['vm'][i]['port']
        vmtime = data['layer_info']['vm'][i]['vmTime']
        log_set.add((vmname, port, vmtime))

    # sort by time
    log_list = list(log_set)
    log_list.sort(key=lambda x: x[2])

    # read log_list and write it into database
    for obj in log_list:
        if isinstance(obj, tuple):
            vmname = obj[0]
            port = obj[1]
            vmtime = obj[2]
            cur.execute(
                "insert into presentation_vm_connect(vmname,port,vmtime) values(%s,%s,%s)",
                [vmname, port, vmtime])
    f.close()

    # 打开主机行为SSH和虚拟机的JSON文件读取数据
    d = open(os.getcwd() + '/JsonFileLog/report_log_filesystem.json', "r", encoding='UTF-8')
    data = json.load(d)

    # 向数据库中传入SSH的相关信息
    # remove duplicates in data['layer_info']['FileEvent']
    log_set = set()
    for i in range(len(data['layer_info']['FileEvent'])):
        directory = data['layer_info']['FileEvent'][i]['directory']
        file = data['layer_info']['FileEvent'][i]['file']
        event = data['layer_info']['FileEvent'][i]['event']
        time = data['layer_info']['FileEvent'][i]['time']
        log_set.add((directory,file,event,time))

    # sort by time
    log_list = list(log_set)
    log_list.sort(key= lambda x:x[3])

    # read log_list and write it into database
    for obj in log_list:
        if isinstance(obj, tuple):
            directory = obj[0]
            file = obj[1]
            event = obj[2]
            time = obj[3]
            cur.execute(
            "insert into presentation_filesystem(directory,file,event,time) values(%s,%s,%s,%s)",
            [directory, file, event, time])

    d.close()
    cur.close()
    conn.commit()
    conn.close()
    print("主机行为数据存储成功")
